package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import entity.Execution;
import entity.Invoice;
import entity.Payment;
import util.DBHelper;


public class InvoiceDAO {
	private Connection conn = null;
	private Statement st = null;

	public InvoiceDAO() {
		try {
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433;DatabaseName=Contract", "sa",
					"123456");
			st = conn.createStatement();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public synchronized void release() {
		if (st != null) {
			try {
				st.close();
			} catch (Exception e) {
			}
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	public synchronized void addInvoice(Invoice Invoice) throws Exception {
		try {
			ResultSet rs = st.executeQuery("select * from Invoice where pno='" + Invoice.getPno() + "'");
			if (rs.next()) {
				throw new Exception("发票号不能重复");
			} else {
				String sql = "insert into Invoice(pno,cno,paystate,paytimeno,pprice,ptime) values('" + Invoice.getPno()
						+ "','" + Invoice.getCno() + "','" + Invoice.getPaystate() + "','" + Invoice.getPaytimeno()
						+ "','" + Invoice.getPprice() + "','" + Invoice.getPtime() + "')";
				st.execute(sql);
			}
			ResultSet rs2 = st.executeQuery("select * from Payment where cno='" + Invoice.getCno() + "';"); // SQL语句
			if (!rs2.next()) {
				throw new Exception("不存在此合同");
			} else {
				Payment pay = new Payment();
				pay.setCno(rs2.getString("cno"));
				pay.setPaystate(rs2.getString("paystate"));
				pay.setContractp(rs2.getString("contractp"));
				pay.setPayprice(rs2.getString("payprice"));
				pay.setPaytimes(rs2.getString("paytimes"));
				int sum = Integer.valueOf(Invoice.getPprice()) + Integer.valueOf(pay.getPayprice());
				String s = String.valueOf(sum);
				String sql2 = "update payment set payprice='" + s + "' where cno='" + pay.getCno() + "'";
				st.execute(sql2);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库错误");
		}

	}

	public synchronized void deltInvoice(String pno) throws Exception {
		try {
			ResultSet rs = st.executeQuery("select * from invoice where pno='" + pno + "'");
			if (!rs.next()) {
				throw new Exception("发票号不存在");
			} else {
				Invoice v = new Invoice();
				v.setCno(rs.getString("cno"));
				String cno = v.getCno();
				v.setPprice(rs.getString("pprice"));
				String sql = "delete from invoice where pno='" + pno + "'";
				st.execute(sql);

				ResultSet rs2 = st.executeQuery("select * from Payment where cno='" + cno + "';"); // SQL语句
				if (!rs2.next()) {
					throw new Exception("数据库错误");
				} else {
					Payment pay = new Payment();
					pay.setCno(rs2.getString("cno"));
					pay.setPaystate(rs2.getString("paystate"));
					pay.setContractp(rs2.getString("contractp"));
					pay.setPayprice(rs2.getString("payprice"));
					pay.setPaytimes(rs2.getString("paytimes"));
					int sum = Integer.valueOf(pay.getPayprice()) - Integer.valueOf(v.getPprice());
					String s = String.valueOf(sum);
					String sql2 = "update payment set payprice='" + s + "' where cno='" + pay.getCno() + "'";
					st.execute(sql2);
				}

			}
		} catch (SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库错误");
		}
	}

	public void editInvoice(Invoice fp) {
		try {
			ResultSet rs = st.executeQuery("select * from invoice where pno='" + fp.getPno() + "'");
			if (!rs.next()) {
				throw new Exception("发票号不存在");
			} else {
				Invoice inv = new Invoice();
				inv.setPno(rs.getString("pno"));
				inv.setCno(rs.getString("cno"));
				inv.setPprice(rs.getString("pprice"));
				String sql = "update invoice set paytimeno='" + fp.getPaytimeno() + "',ptime='" + fp.getPtime()
						+ "',pprice='" + fp.getPprice() + "' where pno='" + fp.getPno() + "'";
				st.execute(sql);
				
				ResultSet rs2 = st.executeQuery("select * from Payment where cno='" + inv.getCno() + "';"); // SQL语句
				if (!rs2.next()) {
					throw new Exception("数据库错误");
				} else {
					Payment pay = new Payment();
					pay.setCno(rs2.getString("cno"));
					pay.setPaystate(rs2.getString("paystate"));
					pay.setContractp(rs2.getString("contractp"));
					pay.setPayprice(rs2.getString("payprice"));
					pay.setPaytimes(rs2.getString("paytimes"));
					int sum = Integer.valueOf(pay.getPayprice()) - Integer.valueOf(inv.getPprice())
							+ Integer.valueOf(fp.getPprice());
					String s = String.valueOf(sum);
					String sql2 = "update payment set payprice='" + s + "' where cno='" + pay.getCno() + "'";
					st.execute(sql2);
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public ArrayList<Invoice> getAllInvoice() {
		ArrayList<Invoice> list = new ArrayList<Invoice>();
		try {
			ResultSet rs = st.executeQuery("select * from Invoice;");
			while (rs.next()) {
				Invoice pay = new Invoice();
				pay.setPno(rs.getString("pno"));
				pay.setCno(rs.getString("cno"));
				pay.setPaystate(rs.getString("paystate"));
				pay.setPaytimeno(rs.getString("paytimeno"));
				pay.setPprice(rs.getString("pprice"));
				pay.setPtime(rs.getString("ptime"));
				list.add(pay);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
		return list;
	}

	public ArrayList<Invoice> getInvoiceByCno(String cno) {
		ArrayList<Invoice> list = new ArrayList<Invoice>();
		try {
			ResultSet rs = st.executeQuery("select * from Invoice where cno='" + cno + "';");
			while (rs.next()) {
				Invoice pay = new Invoice();
				pay.setPno(rs.getString("pno"));
				pay.setCno(rs.getString("cno"));
				pay.setPaystate(rs.getString("paystate"));
				pay.setPaytimeno(rs.getString("paytimeno"));
				pay.setPprice(rs.getString("pprice"));
				pay.setPtime(rs.getString("ptime"));
				list.add(pay);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}
		return list;
	}

	// 链接数据库，从数据库里：根据商品编号获得商品资料
	public Invoice getInvoiceByPno(String pno) {
		try {
			String sql = "select * from invoice where pno='" + pno + "';"; // SQL语句
			ResultSet rs = st.executeQuery(sql);
			if (rs.next()) {
				Invoice pay = new Invoice();
				pay.setPno(rs.getString("pno"));
				pay.setCno(rs.getString("cno"));
				pay.setPaystate(rs.getString("paystate"));
				pay.setPaytimeno(rs.getString("paytimeno"));
				pay.setPprice(rs.getString("pprice"));
				pay.setPtime(rs.getString("ptime"));
				return pay;
			} else {
				return null;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			return null;
		}

	}

	// 获取最近浏览的前五条商品信息
	public ArrayList<Invoice> getViewList(String list) {
		System.out.println("list:" + list);
		ArrayList<Invoice> executionlist = new ArrayList<Invoice>();
		int iCount = 5; // 每次返回前五条记录
		if (list != null && list.length() > 0) {
			String[] arr = list.split(",");
			System.out.println("arr.length=" + arr.length);
			if (arr.length >= 5) {// 如果商品记录大于等于5条
				// (i=8-1;i>=3;i--)
				for (int i = arr.length - 1; i >= arr.length - iCount; i--) {
					executionlist.add(getInvoiceByPno(arr[i]));
				}
			} else {// 如果商品小于5条
				for (int i = arr.length - 1; i >= 0; i--) {
					executionlist.add(getInvoiceByPno(arr[i]));
				}
			}
			return executionlist;
		} else {
			return null;
		}

	}
	public synchronized List<Integer> acquire() throws Exception {
		List<Integer> data = new ArrayList<Integer>();
		try {
			ResultSet rs = st.executeQuery("select sum(convert(int,pprice)) from invoice where ptime>='2017-01-01' and  ptime<='2017-03-31' and paystate='收'");
			while (rs.next()){
				data.add(rs.getInt(1)); 
		    }
		    rs = st.executeQuery("select sum(convert(int,pprice)) from invoice where ptime>='2017-04-01' and  ptime<='2017-06-30' and paystate='收'");
			while (rs.next()){
				data.add(rs.getInt(1)); 
		    }
		    rs = st.executeQuery("select sum(convert(int,pprice)) from invoice where ptime>='2017-07-01' and  ptime<='2017-09-30' and paystate='收'");
			while (rs.next()){
				data.add(rs.getInt(1)); 
		    }
		    rs = st.executeQuery("select sum(convert(int,pprice)) from invoice where ptime>='2017-10-01' and  ptime<='2017-12-31' and paystate='收'");
			while (rs.next()){
				data.add(rs.getInt(1)); 
		    }
		    rs = st.executeQuery("select sum(convert(int,pprice)) from invoice where ptime>='2017-01-01' and  ptime<='2017-03-31' and paystate='付'");
			while (rs.next()){
				data.add(rs.getInt(1)); 
		    }
		    rs = st.executeQuery("select sum(convert(int,pprice)) from invoice where ptime>='2017-04-01' and  ptime<='2017-06-30' and paystate='付'");
			while (rs.next()){
				data.add(rs.getInt(1)); 
		    }
		    rs = st.executeQuery("select sum(convert(int,pprice)) from invoice where ptime>='2017-07-01' and  ptime<='2017-09-30' and paystate='付'");
			while (rs.next()){
				data.add(rs.getInt(1)); 
		    }
		    rs = st.executeQuery("select sum(convert(int,pprice)) from invoice where ptime>='2017-10-01' and  ptime<='2017-12-31' and paystate='付'");
			while (rs.next()){
				data.add(rs.getInt(1)); 
		    }
			return data;
		}
		 catch (SQLException ex) {
			ex.printStackTrace();
			throw new Exception("数据库查询错误");
		}

	}


}